use std::io;
use std::io::Write;
use std::ops::Deref;
use std::sync::Arc;

use serde::{Deserialize, Serialize};
use serde_json::Value;
use sqlx::{Encode, FromRow, Pool, QueryBuilder, Row, Sqlite, Type};
use sqlx::sqlite::{SqliteQueryResult, SqliteRow};
use tracing::error;

use fast_spider::CRUD;

pub static BILI_PICTURE_TAG_DDL: &str = r#"
CREATE TABLE if not exists "bili_picture_tag"
(
    item_id     integer,
    jump_url    text,
    mid         integer,
    orientation integer,
    poi         text,
    schema_url  text,
    source_type integer,
    text        text,
    tid         integer,
    type        integer,
    url         text,
    x           integer,
    y           integer,
    img_src     text not null
        constraint bili_picture_tag_bili_picture_img_src_fk
            references bili_picture
)"#;

/// 哔哩哔哩图片 Tag
#[derive(Debug, Clone, Serialize, Deserialize, FromRow, Eq, PartialEq)]
pub struct BiliPictureTagModel {
    pub item_id: i64,
    pub jump_url: String,
    pub mid: i64,
    pub orientation: i64,
    pub poi: String,
    pub schema_url: String,
    pub source_type: i64,
    pub text: String,
    pub tid: i64,
    #[serde(rename(serialize = "type", deserialize = "type"))]
    pub tag_type: i64,
    pub url: String,
    pub x: i64,
    pub y: i64,
    pub img_src: String,
}

impl TryFrom<&SqliteRow> for BiliPictureTagModel {
    type Error = anyhow::Error;

    fn try_from(row: &SqliteRow) -> Result<Self, Self::Error> {
        Ok(Self {
            item_id: row.try_get("item_id")?,
            jump_url: row.try_get("jump_url")?,
            mid: row.try_get("mid")?,
            orientation: row.try_get("orientation")?,
            poi: row.try_get("poi")?,
            schema_url: row.try_get("schema_url")?,
            source_type: row.try_get("source_type")?,
            text: row.try_get("text")?,
            tid: row.try_get("tid")?,
            tag_type: row.try_get("type")?,
            url: row.try_get("url")?,
            x: row.try_get("x")?,
            y: row.try_get("y")?,
            img_src: row.try_get("img_src")?,
        })
    }
}

impl TryFrom<SqliteRow> for BiliPictureTagModel {
    type Error = anyhow::Error;

    fn try_from(row: SqliteRow) -> Result<Self, Self::Error> {
        Ok(Self::try_from(&row)?)
    }
}

impl TryFrom<&Value> for BiliPictureTagModel {
    type Error = anyhow::Error;

    fn try_from(v: &Value) -> Result<Self, Self::Error> { unimplemented!() }
}

impl TryFrom<Value> for BiliPictureTagModel {
    type Error = anyhow::Error;

    fn try_from(v: Value) -> Result<Self, Self::Error> {
        Self::try_from(&v)
    }
}

impl BiliPictureTagModel {
    pub async fn insert(&self, db: &Pool<Sqlite>) -> SqliteQueryResult {
        let mut qb = QueryBuilder::new("insert into bili_picture_tag ");
        let qb = qb.push("(item_id, jump_url, mid, orientation, poi, schema_url, source_type, text, tid, type, url, x, y, img_src)")
                   .push(" values (")
                   .push_bind(self.item_id)
                   .push(',')
                   .push_bind(&self.jump_url)
                   .push(',')
                   .push_bind(self.mid)
                   .push(',')
                   .push_bind(self.orientation)
                   .push(',')
                   .push_bind(&self.poi)
                   .push(',')
                   .push_bind(&self.schema_url)
                   .push(',')
                   .push_bind(self.source_type)
                   .push(',')
                   .push_bind(&self.text)
                   .push(',')
                   .push_bind(self.tid)
                   .push(',')
                   .push_bind(self.tag_type)
                   .push(',')
                   .push_bind(&self.url)
                   .push(',')
                   .push_bind(self.x)
                   .push(',')
                   .push_bind(self.y)
                   .push(',')
                   .push_bind(&self.img_src)
                   .push(')');
        println!("{}", qb.sql());
        io::stdout().flush().unwrap();
        let query = qb.build();
        query.execute(db)
             .await
             .unwrap_or_else(|err| {
                 error!("{}", err);
                 SqliteQueryResult::default()
             })
    }

    pub async fn delete(&self, db: &Pool<Sqlite>) -> SqliteQueryResult {
        let mut qb = QueryBuilder::new("delete from bili_picture_tag where item_id=");
        let qb = qb.push_bind(&self.item_id);
        let query = qb.build();
        query.execute(db)
             .await
             .unwrap_or_else(|err| {
                 error!("{}", err);
                 SqliteQueryResult::default()
             })
    }

    pub async fn select_one<T>(db: &Pool<Sqlite>, col: &str, v: T) -> Option<Self>
        where for<'a> T: Encode<'a, Sqlite> + Send + Type<Sqlite>
    {
        let mut qb = QueryBuilder::new("select * from bili_picture_tag where ");
        let qb = qb.push(col)
                   .push("=")
                   .push_bind(v);
        let query = qb.build();
        match query.fetch_one(db).await {
            Ok(row) => Self::try_from(row).ok(),
            Err(err) => {
                error!("{}", err);
                None
            }
        }
    }
}


#[cfg(test)]
mod tests {
    use std::sync::Arc;
    use crate::db::create_test_db;

    use crate::models::bili::picture::BiliPictureModel;
    use crate::models::bili::tag::BiliPictureTagModel;

    #[async_std::test]
    async fn test_bili_picture_tag() {
        todo!()
    }
}
